from openpyxl import load_workbook
from openpyxl_image_loader import SheetImageLoader
import os
from datetime import datetime
from openpyxl.utils import range_boundaries


#来源：https://blog.csdn.net/weixin_44380466/article/details/131864657
#来源：https://blog.csdn.net/weixin_46783197/article/details/139320666

# 获取每一个sheet表格有效数据量
def get_excel_num(excelname):
    workbook = load_workbook('upload/excel/sample/'+ excelname +'.xlsx')
    sheet_names = workbook.sheetnames
    num = 0
    data = []
    total = []
    for key,item in enumerate(sheet_names):
        sheet = workbook[sheet_names[key]]
        num = 0
        for index, row in enumerate(sheet.rows, start=1):
            if row[3].value == None and row[4].value == None :
                continue
            name = row[3].value
            num+=1
        total.append(num)
        obj = {"sheetname":sheet_names[key],"total_num":num}
        data.append(obj)
    return {'totals':total,'detail':data}

def get_excel_data(excel_total_data,excelname = "product"):
# 选择工作表
    folder_paths,ymd = folder_path('excel')
    workbook = load_workbook('upload/excel/sample/'+ excelname +'.xlsx')
    sheet_names = workbook.sheetnames
    for key,item in enumerate(sheet_names):
        sheet = workbook[item]
        image_loader = SheetImageLoader(sheet)
        path = folder_paths + "/" + item
        if not os.path.exists(path):
            os.makedirs(path)

        # 获取合并单元格数据的最大值和最小值
        merged_cell_range = list(sheet.merged_cells.ranges)
        merged_cell_C2 = [cell for cell in merged_cell_range if cell.coord[0]=='C']
        merged_data = []
        for merged_cell in merged_cell_C2:
            min_col, min_row, max_col, max_row = merged_cell.bounds
            obj = {'min':min_row,'max':max_row} 
            merged_data.append(obj)
        print(item,merged_data)

        # excel表中id,barcode字典
        excel_barcodeList = ['0']
        for index, row in enumerate(sheet.rows, start=1):
            data = {"id": index,"barcode":row[3].value}
            if index <= int(excel_total_data[key]):
                excel_barcodeList.append(row[3].value)
        print(excel_barcodeList,len(excel_barcodeList))
        
        for index, row in enumerate(sheet.rows, start=1):
            if row[3].value == None:
                continue
            name = row[3].value
            print(index,name)
            if image_loader.image_in(f'C{index}'):
                image = image_loader.get(f'C{index}')
                if name != None :
                    if '\n' in name:
                        name = name.split('\n')[0]
                    image.save(path+'/'+ name + ".png")
                    for item in merged_data:
                        if(index >= item['min'] and index <= item['max']):
                            for i in range(item['min'], item['max']+1):
                                name = excel_barcodeList[i]
                                if name != None :
                                    if '\n' in name:
                                        name = name.split('\n')[0]
                                    image.save(path+'/'+ name + ".png")

        image_loader._images.clear()
        workbook.close()
    return {'time': ymd}

def time_ymd():
    now = datetime.now()
    year = now.year
    month = now.month
    day = now.day
    ymd = str(year) + '-' + str(month) + '-' + str(day)
    return ymd


def folder_path(folderName,dir="download"):
    type_path = os.path.join(dir, folderName)
    ymd = time_ymd()
    time_path = os.path.join(type_path, ymd)
    return time_path,ymd

